﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;


namespace NTQOnlineExam.Services.DBCommand
{
    public class DBCommand
    {
        public static DBCommand CreateInstant()
        {
            return new DBCommand();
        }

        #region ReportService
        public DataTable GetQuestionsDbCommand(string keyword, int termId, int sectionId, int pageIndex, int pageSize, string sorting, out int totalRecord)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var ds = new DataSet();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("[SearchQuestions]", con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                    if (sectionId > 0)
                    {
                        cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = sectionId;
                    }
                    if (termId > 0)
                    {
                        cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = termId;
                    }
                    cmd.Parameters.Add("@FirstRecord", SqlDbType.Int).Value = pageIndex;
                    cmd.Parameters.Add("@RecsPerPage", SqlDbType.Int).Value = pageSize;
                    var output = new SqlParameter("@TotalRecord", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(output);
                    con.Open();
                    adapt.Fill(ds);
                    cmd.ExecuteNonQuery();
                    totalRecord = (int)output.Value;
                }
                return ds.Tables[0];
            }
        }

        public DataTable GetAnswerDbCommand(int questionId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("[SearchAnswer]", con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionId;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return dt;
            }
        }



        public DataTable GetSectionDbCommand(int termId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("[SearchSection]", con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = termId;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return dt;
            }
        }

        public DataTable GetTermDbCommand()
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            var dt = new DataTable();
            using (var con = new SqlConnection(connectionString))
            {
                using (var cmd = new SqlCommand("[SearchTerm]", con))
                {
                    var adapt = new SqlDataAdapter(cmd);
                    adapt.SelectCommand.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    adapt.Fill(dt);
                    cmd.ExecuteNonQuery();
                }
                return dt;
            }
        }
        #endregion

        #region CommandService

        public bool CreateAnswerDbCommand(int questionId, string content, out int answerId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[CreateAnswer]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionId;
                        cmd.Parameters.Add("@Content", SqlDbType.NText).Value = content;
                        var output = new SqlParameter("@AnswerId", SqlDbType.Int)
                        {
                            Direction = ParameterDirection.Output
                        };
                        cmd.Parameters.Add(output);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        answerId = (int)output.Value;
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                answerId = 0;
                return false;
            }
        }

        public bool UpdateAnswerDbCommand(UpdateAnswerCommand updateAnswerCommand)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[UpdateAnswer]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = updateAnswerCommand.QuestionId;
                        cmd.Parameters.Add("@Content", SqlDbType.NText).Value = updateAnswerCommand.Content;
                        cmd.Parameters.Add("@AnswerId", SqlDbType.Int).Value = updateAnswerCommand.AnswerId;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool DeleteAnswerDbCommand(int answerId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[DeleteAnswer]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@AnswerId", SqlDbType.Int).Value = answerId;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool UpdateQuestionDbCommand(UpdateQuestionCommand questionCommand)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[UpdateQuestion]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionCommand.QuestionId;
                        cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = questionCommand.SectionId;
                        cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = questionCommand.TermId;
                        cmd.Parameters.Add("@Content", SqlDbType.NText).Value = questionCommand.Content;
                        cmd.Parameters.Add("@AnswerId", SqlDbType.Int).Value = questionCommand.AnswerId;
                        cmd.Parameters.Add("@Explaination", SqlDbType.NVarChar).Value = questionCommand.Explaination;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool CreateQuestionDbCommand(AddQuestionCommand questionCommand, out int questionId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[CreateQuestion]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@SectionId", SqlDbType.Int).Value = questionCommand.SectionId;
                        cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = questionCommand.TermId;
                        cmd.Parameters.Add("@Content", SqlDbType.NText).Value = questionCommand.Content;
                        cmd.Parameters.Add("@AnswerId", SqlDbType.Int).Value = questionCommand.AnswerId;
                        cmd.Parameters.Add("@Explaination", SqlDbType.NVarChar).Value = questionCommand.Explaination;
                        var output = new SqlParameter("@QuestionId", SqlDbType.Int)
                        {
                            Direction = ParameterDirection.Output
                        };
                        cmd.Parameters.Add(output);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        questionId = (int)output.Value;
                    }
                }
                return true;
            }
            catch (Exception exception)
            {
                questionId = 0;
                return false;
            }
        }

        public bool DeleteQuestionDbCommand(int questionId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    using (var cmd = new SqlCommand("[DeleteQuestion]", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@QuestionId", SqlDbType.Int).Value = questionId;
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }


        public bool CreateExaminationDbCommand(AddExaminationCommand addExaminationCommand, out int examinationId)
        {
            var connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
            try
            {
                using (var con = new SqlConnection(connectionString))
                {
                    // Start a local transaction.
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("SampleTransaction");
                    using (var cmd = new SqlCommand("[CreateExam]", con))
                    {
                        cmd.Transaction = transaction;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = addExaminationCommand.UserId;
                        cmd.Parameters.Add("@TermId", SqlDbType.Int).Value = addExaminationCommand.TermId;
                        cmd.Parameters.Add("@Score", SqlDbType.Int).Value = addExaminationCommand.Score;
                        var output = new SqlParameter("@ExaminationId", SqlDbType.Int)
                        {
                            Direction = ParameterDirection.Output
                        };
                        cmd.Parameters.Add(output);

                        cmd.ExecuteNonQuery();
                        examinationId = (int)output.Value;
                    }
                    var examDetailQuery = BuildExamDetailQuery(addExaminationCommand, examinationId);
                    if (!string.IsNullOrWhiteSpace(examDetailQuery))
                    {
                        using (var cmd = new SqlCommand(examDetailQuery, con))
                        {
                            cmd.Transaction = transaction;
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();

                }
                return true;
            }
            catch (Exception exception)
            {
                examinationId = 0;
                return false;
            }
        }

        private string BuildExamDetailQuery(AddExaminationCommand addExaminationCommand, int examId)
        {
            if (addExaminationCommand.QuestionAnswers == null || addExaminationCommand.QuestionAnswers.Count == 0)
                return string.Empty;
            var query = new StringBuilder();
            foreach (var questionAnwser in addExaminationCommand.QuestionAnswers)
            {
                query.AppendFormat(" INSERT INTO dbo.ExamDetail(QuestionId,AnswerId,ExamId) VALUES ({0},{1},{2});", questionAnwser.Key, questionAnwser.Value, examId);
            }
            return query.ToString();
        }

        #endregion

    }
}
